package com.service.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.base.CommonMethods;
import com.dao.BaseDao;
import com.dao.impl.BaseDaoImpl;
import com.dao.impl.SMSDaoImpl;
import com.domain.ClassInfo;
import com.domain.Model;
import com.domain.Page;
import com.domain.SMSBook;
import com.domain.SMSInfo;
import com.domain.SMSModel;
import com.domain.User;
import com.service.SMSService;

@Repository("smsService")
public class SMSServiceImpl implements SMSService {

	@Autowired
	private SMSDaoImpl smsdao;

	/**
	 * 获取短信信息
	 * 
	 * @param page
	 * @return
	 * @throws Exception
	 */
	@Override
	public String getSMSInfo(Page page, User user, String teamText,
			String conText, String NoText, String cdate1, String cdate2,
			String sender) throws Exception {
		String userName = user.getUsername();
		String role = user.getRole();
		StringBuilder sb = new StringBuilder();
		StringBuilder sbC = new StringBuilder();
		/**
		 * 教师
		 */
		if (role.equals("t")) {
			sb.append("SELECT sinfo.`id`,sinfo.`teamName`,sinfo.`phoneNum`,sinfo.`contents`,sinfo.`createDate` FROM smsinfo sinfo LEFT JOIN userinfo uinfo ON sinfo.`sender` = uinfo.`id` WHERE uinfo.`userId` = '"
					+ userName + "'  AND sinfo.isDel = 0 ");
			sbC.append("SELECT count(sinfo.id) FROM smsinfo sinfo LEFT JOIN userinfo uinfo ON sinfo.`sender` = uinfo.`id` WHERE uinfo.`userId` = '"
					+ userName + "'  AND sinfo.isDel = 0 ");
			if (!CommonMethods.isEmpty(teamText)) { // 收信人
				// byte[] t = teamText.getBytes("iso-8859-1");
				// teamText = new String(t);
				sb.append("AND sinfo.teamName LIKE '%" + teamText + "%' ");
				sbC.append("AND sinfo.teamName LIKE '%" + teamText + "%' ");
			}
			if (!CommonMethods.isEmpty(NoText)) { // 手机号码
				sb.append("AND sinfo.phoneNum LIKE '%" + NoText + "%' ");
				sbC.append("AND sinfo.phoneNum LIKE '%" + NoText + "%' ");
			}
			if (!CommonMethods.isEmpty(conText)) { // 短信内容
				sb.append("AND sinfo.contents LIKE '%" + conText + "%' ");
				sbC.append("AND sinfo.contents LIKE '%" + conText + "%' ");
			}
			if (!CommonMethods.isEmpty(cdate1)) { // 创建时间
				sb.append("AND sinfo.createDate >= '" + cdate1 + "' ");
				sbC.append("AND sinfo.createDate >= '" + cdate1 + "' ");
			}
			if (!CommonMethods.isEmpty(cdate2)) { // 创建时间
				sb.append("AND sinfo.createDate <= '" + cdate2 + "' ");
				sbC.append("AND sinfo.createDate <= '" + cdate2 + "' ");
			}
		} else { // 管理员登录
			sb.append("SELECT sinfo.`id`,sinfo.`teamName`,sinfo.`phoneNum`,sinfo.`contents`,sinfo.`createDate` FROM smsinfo sinfo "
					+ "LEFT JOIN userinfo uinfo ON sinfo.`sender` = uinfo.`id` WHERE true AND sinfo.isDel = 0 ");
			sbC.append("SELECT count(*) FROM smsinfo sinfo "
					+ "LEFT JOIN userinfo uinfo ON sinfo.`sender` = uinfo.`id` WHERE true AND sinfo.isDel = 0 ");
			if (!CommonMethods.isEmpty(teamText)) { // 收信人
				sb.append("AND sinfo.teamName LIKE '%" + teamText + "%' ");
				sbC.append("AND sinfo.teamName LIKE '%" + teamText + "%' ");
			}
			if (!CommonMethods.isEmpty(NoText)) { // 手机号码
				sb.append("AND sinfo.phoneNum LIKE '%" + NoText + "%' ");
				sbC.append("AND sinfo.phoneNum LIKE '%" + NoText + "%' ");
			}
			if (!CommonMethods.isEmpty(conText)) { // 短信内容
				sb.append("AND sinfo.contents LIKE '%" + conText + "%' ");
				sbC.append("AND sinfo.contents LIKE '%" + conText + "%' ");
			}
			if (!CommonMethods.isEmpty(sender)) { // 发送人姓名
				sb.append("AND uinfo.`name` LIKE '%" + sender + "%' ");
				sbC.append("AND uinfo.`name` LIKE '%" + sender + "%' ");
			}
			if (!CommonMethods.isEmpty(cdate1)) { // 创建时间
				sb.append("AND sinfo.createDate >= '" + cdate1 + "' ");
				sbC.append("AND sinfo.createDate >= '" + cdate1 + "' ");
			}
			if (!CommonMethods.isEmpty(cdate2)) { // 创建时间
				sb.append("AND sinfo.createDate <= '" + cdate2 + "' ");
				sbC.append("AND sinfo.createDate <= '" + cdate2 + "' ");
			}

		}
		sb.append(" LIMIT " + page.getStart() + "," + page.getLimit());
		System.out.println("sql1:" + sb.toString());
		System.out.println("sql2:" + sbC.toString());

		List<SMSInfo> list = (List<SMSInfo>) smsdao.queData(sb.toString());
		// 获取总结果数量
		int count = (Integer) smsdao.getDataCount(sbC.toString());
		JSONArray arr = JSONArray.fromObject(list);
		String json = "{\"total\":" + count + ",\"root\":" + arr + "}";
		return json;
	}

	@Override
	public boolean delSMSInfo(String id) {
		String sql = "UPDATE smsinfo AS sms SET sms.isDel = 1 WHERE id = 11"
				+ id;
		return smsdao.delData(sql);
	}

	@Override
	public String getAddress(User user, int isAll) {
		String role = user.getRole();
		String userName = user.getUsername();
		String sql = "";

		List<ClassInfo> list = new ArrayList<ClassInfo>();
		ClassInfo cinfo = new ClassInfo("0", "全部");
		list.add(cinfo);
		if (role.equals("t") && isAll == 0) { // 教师
			sql = "SELECT DISTINCT  cla.`id`,cla.`name` FROM classinfo cla LEFT JOIN userinfo uinfo ON cla.`teacherId` = uinfo.`id` WHERE uinfo.`userId` = '"
					+ userName + "'";
		} else if (role.equals("t") && isAll == 1) {
			sql = "SELECT DISTINCT  cla.`id`,cla.`name` FROM classinfo cla LEFT JOIN userinfo uinfo ON cla.`teacherId` = uinfo.`id`  WHERE cla.`teacherId` <> 1";
		} else if (role.equals("s") && isAll == 1) {
			sql = "SELECT DISTINCT c.`id`,c.`name` FROM studentinfo AS s LEFT JOIN classinfo AS c ON s.`classId` = c.`id` WHERE role = 1";
		} else if (role.equals("s") && isAll == 0) {
			cinfo = new ClassInfo("-1", "教师");
			list.add(cinfo);
			cinfo = new ClassInfo("-2", "学生");
			list.add(cinfo);
		}
		if (!sql.equals("") && sql != null) {
			list.addAll((List<ClassInfo>) smsdao.queAddress(sql));
		}
		JSONArray arr = JSONArray.fromObject(list);
		return "{\"root\":" + arr.toString() + "}";
	}

	@Override
	public String getModel() {
		String sql = "";
		sql = "SELECT DISTINCT mo.`name`,mo.`contents` FROM smsmodel mo";
		List<Model> list = new ArrayList<Model>();
		if (!sql.equals("") && sql != null) {
			list.addAll((List<Model>) smsdao.queModel(sql));
		}
		JSONArray arr = JSONArray.fromObject(list);
		return "{\"root\":" + arr.toString() + "}";
	}

	/**
	 * 根据收信人的id获取收信人详细信息
	 * 
	 * @throws SQLException
	 */
	@Override
	public String getAddressInfo(String id, User user) throws SQLException {
		String userName = user.getUsername();
		String sql = "SELECT GROUP_CONCAT(CONCAT(NAME,'(',phoneNum,')')) AS VALUE FROM studentinfo WHERE classId = "
				+ id + "";
		if (id.equals("0") && user.getRole().equals("t")) { // 所有班级
			sql = "SELECT GROUP_CONCAT(CONCAT(stu.NAME,'(',stu.phoneNum,')')) AS VALUE FROM studentinfo AS stu LEFT JOIN "
					+ "classinfo cla ON stu.classId = cla.id LEFT JOIN userinfo AS uinfo ON "
					+ "cla.teacherId = uinfo.`id` WHERE uinfo.`userId` = '"
					+ userName + "'";
		} else if (id.equals("-1")) { // 所有教师
			sql = "SELECT GROUP_CONCAT(CONCAT(s.NAME,'(',s.phoneNum,')')) AS VALUE FROM studentinfo AS s WHERE s.role = 2 ";
		} else if (id.equals("-2")) { // 所有学生
			sql = "SELECT GROUP_CONCAT(CONCAT(s.NAME,'(',s.phoneNum,')')) AS VALUE FROM studentinfo AS s WHERE s.role = 1 ";
		} else if (id.equals("0") && user.getRole().equals("s")) { // 所有教师和学生
			sql = "SELECT GROUP_CONCAT(CONCAT(s.NAME,'(',s.phoneNum,')')) AS VALUE FROM studentinfo AS s";
		}
		String result = (String) smsdao.queAddressInfo(sql);
		String[] arr = result.split(",");
		int numT = arr.length > 10 ? 10 : arr.length - 1;
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < numT; i++) {
			sb.append(arr[i] + ",");
		}
		return sb.substring(0, sb.length() - 1);
	}

	@Override
	public boolean sendSMS(String teamId, String teamName, String mes, User user)
			throws SQLException {
		String userName = user.getUsername();
		String sql = "INSERT INTO smsinfo(teamName,phoneNum,contents,sender) SELECT '"
				+ teamName
				+ "',s.phoneNum,'"
				+ mes
				+ "',u.id AS VALUE FROM studentinfo AS s LEFT JOIN userinfo AS u ON TRUE WHERE u.userid = '"
				+ userName + "' AND s.role = 1 AND s.classId = " + teamId;
		if (teamId.equals("0") && user.getRole().equals("t")) {
			sql = "INSERT INTO smsinfo(teamName,phoneNum,contents,sender) SELECT c.name,s.phoneNum,'"
					+ mes
					+ "',u.id AS VALUE FROM studentinfo AS s LEFT JOIN userinfo AS u ON TRUE LEFT JOIN classinfo AS c ON c.teacherId = u.id WHERE u.userid = '"
					+ userName + "' AND  s.classid = c.id ";
		} else if (teamId.equals("-1")) { // 所有教师
			sql = "INSERT INTO smsinfo(teamName,phoneNum,contents,sender) SELECT '教师',s.phoneNum,'"
					+ mes
					+ "',u.id AS VALUE FROM studentinfo AS s LEFT JOIN userinfo AS u ON TRUE WHERE u.userid = '"
					+ userName + "' AND s.role = 2";
		} else if (teamId.equals("-2")) { // 所有学生

			sql = "INSERT INTO smsinfo(teamName,phoneNum,contents,sender) SELECT c.name,s.phoneNum,'mes',u.id AS VALUE FROM studentinfo AS s LEFT JOIN classinfo AS c ON c.id = s.classid LEFT JOIN userinfo AS u ON TRUE WHERE u.userid = '"
					+ userName + "' AND s.role = 1";
		} else if (teamId.equals("0") && user.getRole().equals("s")) {
			sql = "INSERT INTO smsinfo(teamName,phoneNum,contents,sender) SELECT c.name,s.phoneNum,'mes',u.id AS VALUE FROM studentinfo AS s LEFT JOIN classinfo AS c ON c.id = s.classid LEFT JOIN userinfo AS u ON TRUE WHERE u.userid = '"
					+ userName + "'";
		}
		return smsdao.addData(sql);
	}

	@Override
	public String getSMSModel(Page page) throws SQLException {
		String sql = "SELECT * FROM smsmodel LIMIT " + page.getStart() + ","
				+ page.getLimit();
		List<SMSModel> list = new ArrayList<SMSModel>();
		if (!sql.equals("") && sql != null) {
			list.addAll((List<SMSModel>) smsdao.getSMSModel(sql));
		}
		JSONArray arr = JSONArray.fromObject(list);
		String json = "{\"root\":" + arr + "}";

		return json.toString();
	}

	@Override
	public boolean delSMSModel(String id) {
		String sql = "DELETE  FROM smsmodel WHERE id = '" + id + "'";
		return smsdao.delData(sql);
	}

	@Override
	public boolean addSMSModel(String nameText, String contentText)
			throws Exception {

		// byte[] t = nameText.getBytes("iso-8859-1");
		// nameText = new String(t);
		String sql = "INSERT  INTO smsmodel(name,contents) VALUES ('"
				+ nameText + "','" + contentText + "')";
		return smsdao.addData(sql);
	}

	@Override
	public boolean editSMSModel(String id, String nameText, String contentText)
			throws Exception {

		// byte[] t = nameText.getBytes("iso-8859-1");
		// nameText = new String(t);
		String sql = "UPDATE smsmodel SET NAME = '" + nameText
				+ "',contents = '" + contentText + "' WHERE id = " + id;
		return smsdao.modData(sql);
	}

	@Override
	public String getModelInfo(String id, User user) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String getSMSBook(Page page, int team, int classTeam)
			throws Exception {
		List<SMSBook> list = new ArrayList<SMSBook>();
		String sql = "SELECT t.`id`, t.`name`,t.`phoneNum`,t.`role` FROM teacherinfo AS t LIMIT "
				+ page.getStart() + "," + page.getLimit();
		String sqlC = "SELECT count(1) FROM studentinfo";
		int count = 0;
		if (team == 0) { // 全部联系人\
			sql = "SELECT s.`id`,s.`name`,s.`phoneNum`,s.`role` FROM studentinfo AS s LIMIT "
					+ page.getStart() + "," + page.getLimit();
			sqlC = "SELECT count(1) FROM studentinfo";
		} else if (team == 1) { // 教师
			sql = "SELECT s.`id`,s.`name`,s.`phoneNum`,s.`role` FROM studentinfo AS s WHERE s.role = 2 LIMIT "
					+ page.getStart() + "," + page.getLimit();
			sqlC = "SELECT count(1) FROM studentinfo AS s WHERE s.role = 2";
		} else if (team == 2) { // 学生
			if (classTeam == 0) { // 全部
				sql = "SELECT s.`id`,s.`name`,s.`phoneNum`,s.`role` FROM studentinfo AS s WHERE s.role = 1 LIMIT "
						+ page.getStart() + "," + page.getLimit();
				sqlC = "SELECT count(1) FROM studentinfo AS s WHERE s.role = 1";
			} else { // 班级id
				sql = "SELECT s.`id`,s.`name`,s.`phoneNum`,s.`role` FROM studentinfo AS s WHERE s.classid = "
						+ classTeam
						+ " LIMIT "
						+ page.getStart()
						+ ","
						+ page.getLimit();
				sqlC = "SELECT count(1) FROM studentinfo AS s WHERE s.classid = "
						+ team;
			}
		}
		List<SMSBook> listT = (List<SMSBook>) smsdao.queSMSBook(sql);
		list.addAll(listT);
		// 获取总结果数量
		count += (Integer) smsdao.getDataCount(sqlC);
		JSONArray arr = JSONArray.fromObject(list);
		String json = "{\"total\":" + count + ",\"root\":" + arr + "}";
		return json;
	}

	@Override
	public int getSMSCount(User user) throws Exception {

		String sql = "SELECT count(s.id) FROM smsinfo AS s LEFT JOIN userinfo AS u ON u.`id` = s.`sender` WHERE u.`userId` = '"
				+ user.getUsername() + "'";
		return smsdao.getSMSCount(sql);
	}
}
